/* WorldBank.do (STATA)
	Construct World Bank data.
	by Ralph Koijen & Motohiro Yogo */

#delimit ;
clear all;
set more off;
set type double;

cap log close;
log using WorldBank, replace;

/* Define local and global variables */

local directory = "../../Data/World Bank";

do global;


/* Loop through files */

tempfile WorldBank;

qui foreach file in "API_CM.MKT.LCAP.CD_DS2_en_excel_v2_5608355"
	"API_FP.CPI.TOTL_DS2_en_excel_v2_5607584"
	"API_NE.CON.TOTL.CD_DS2_en_excel_v2_5611059"
	"API_NE.CON.TOTL.KN_DS2_en_excel_v2_5611597"
	"API_NY.GDP.MKTP.CD_DS2_en_excel_v2_5607200"
	"API_NY.GDP.MKTP.KN_DS2_en_excel_v2_5607598"
	"API_NY.GDP.MKTP.PP.CD_DS2_en_excel_v2_5795834"
	"API_NY.GDP.PCAP.PP.CD_DS2_en_excel_v2_5708311"
	"API_PA.NUS.PPP_DS2_en_excel_v2_5607721"
	"API_PA.NUS.PRVT.PP_DS2_en_excel_v2_5607340"
	"API_SP.POP.TOTL_DS2_en_excel_v2_5607126"{;

	/* Import from Excel */

	import excel using "`directory'/`file'",
		cellra(A4) first clear;

	drop CountryName IndicatorName;

	/* Save data */

	if !regexm("`file'","^API_CM.MKT.LCAP.CD_DS2_en_excel_v2_") {;
		append using `WorldBank';
	};

	save `WorldBank', replace;
};

/* Rename variables */

rename CountryCode country;

local i = 1960;

foreach var of varlist E-Z A* B* {;
	rename `var' _`i';

	local i = `i'+1;
};

/* Fix variables */

replace IndicatorCode = subinstr(IndicatorCode,".","",.);

/* Reshape in long format */

reshape long _, i(country IndicatorCode) j(year);
reshape wide _, i(year country) j(IndicatorCode) string;

/* Rename variables */

foreach var in CMMKTLCAPCD FPCPITOTL NECONTOT* NYGDP* PANUS* SPPOPTOTL {;
	rename _`var' `var';
};

rename CMMKTLCAPCD outstand;
rename FPCPITOTL cpi;
rename NECONTOTLCD cons;
rename NECONTOTLKN consR;
rename NYGDPMKTPCD gdp;
rename NYGDPMKTPKN gdpR;
rename NYGDPMKTPPPCD gdpP;
rename NYGDPPCAPPPCD gdpPpc;
rename SPPOPTOTL pop;

/* Convert to billion */

foreach var of varlist outstand cons consR gdp gdpR gdpP pop {;
	replace `var' = `var'/1e9;
};

/* Convert PPP conversion factor to US$ per LCU */

foreach var of varlist PANUS* {;
	replace `var' = 1/`var';
};

/* Sample criteria */

keep if inrange(year,$year_min-1,$year_max+1);

/* Label variables */

order year country;

label var year			"Year";
label var country		"Investor country";

label var outstand		"Market capitalization of listed domestic companies (US$ billion)";
label var cpi			"Consumer price index";
label var cons			"Consumption, Nominal (US$ billion)";
label var consR		 	"Consumption, Real (LCU billion)";
label var gdp			"GDP, Nominal (US$ billion)";
label var gdpR			"GDP, Real (LCU billion)";
label var gdpP			"GDP, Nominal PPP (US$)";
label var gdpPpc		"GDP per capita, Nominal PPP (US$)";
label var PANUSPPP		"PPP conversion factor, GDP (US$ per LCU)";
label var PANUSPRVTPP	"PPP conversion factor, Consumption (US$ per LCU)";
label var pop			"Population (billion)";

/* Save data */

sort year country;

save WorldBank, replace;

log close;
